"""
Case Type   : Compatibility
Case Name   : 指定参数replace,导入唯一键值已存在的数据值,部分测试点合理报错
Create At   : 2024/10/18
Owner       : @zhanghuan96
Description :
        1.创建数据文件
        2.创建兼容B库
        3.建表
        4.创建的表中导入与唯一键重复的值
        5.查询表
        6.截断表
        7.重新创建文件
        8.向创建的表中导入与唯一键不重复的值,其他列数据已经在表中不存在
        9.查询表
        10.截断表
        11.向创建的表中导入与唯一键不重复的值,其他列数据已经在表中存在
        12.重新创建文件
        13.向创建的表中导入与唯一键不重复的值,其他列数据已经在表中存在
        14.查询表
        15.清理环境
Expect      :
        1.创建成功
        2.成功
        3.成功
        4.成功
        5.成功
        6.成功
        7.成功
        8.成功
        9.成功
        10.成功
        11.成功
        12.成功
        13.成功
        14.成功
        15.成功
History     :
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class LoadData0008(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.common = Common()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_user = Node('PrimaryDbUser')
        self.db_name = 'db_load0008'
        self.tb_name = 'tb_load0008'
        self.file_path = os.path.join(macro.DB_BACKUP_PATH,
                                      'load_data0008.txt')

    def test_load(self):
        text1 = '----step1:创建数据文件; expect:创建成功----'
        self.log.info(text1)
        echo_cmd = f'''rm -rf {self.file_path};
                    echo "1+aaa+17+13313692580" >> {self.file_path};
                    echo "2+bbb+18+12213691470" >> {self.file_path};
                    echo "1+ccc+19+13525801560" >> {self.file_path};
                    echo "2+ddd+18+12213691470" >> {self.file_path};
                    echo "5+eee+19+13525801560" >> {self.file_path};
                    '''
        self.log.info(echo_cmd)
        res = self.common.get_sh_result(self.pri_sh.node, echo_cmd)
        self.log.info(res)
        self.assertEqual('', res, '文件写入失败')

        text2 = '----step2:创建兼容B库; expect:创建成功----'
        self.log.info(text2)
        sql_cmd = (f"drop database if exists {self.db_name};"
                   f"create database {self.db_name} dbcompatibility ='B';")

        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('CREATE DATABASE', sql_res, '兼容B库创建失败')

        text = '----step3:建表; expect:建表成功----'
        self.log.info(text)
        sql_cmd1 = (f'''drop table if exists {self.tb_name};
        create table {self.tb_name}(
        c_id int unique,
        c_name char(20),
        c_age int,
        c_num char(11) unique);
        insert into {self.tb_name} values(001,'小翠',16,13223323332);''')
        self.log.info(sql_cmd1)
        sql_res1 = self.pri_sh.execut_db_sql(sql_cmd1, dbname=self.db_name)
        self.log.info(sql_res1)
        self.assertIn('CREATE TABLE', sql_res1, '查询失败')
        self.assertIn('INSERT', sql_res1, '查询失败')

        text = '----step4:创建的表中导入与唯一键重复的值; expect：成功----'
        self.log.info(text)
        sql_cmd2 = (f"load data infile '{self.file_path}' replace into table "
                    f"{self.tb_name} character set 'sql_ascii' "
                    f"fields terminated by '+' ;")
        self.log.info(sql_cmd2)
        sql_res2 = self.pri_sh.execut_db_sql(sql_cmd2, dbname=self.db_name)
        self.log.info(sql_res2)
        self.assertIn('COPY 5', sql_res2, '执行失败' + text)

        text = '----step5:查询表; expect：成功----'
        self.log.info(text)
        sql_cmd3 = f"select * from {self.tb_name};"
        self.log.info(sql_cmd3)
        sql_res3 = self.pri_sh.execut_db_sql(sql_cmd3, dbname=self.db_name)
        self.log.info(sql_res3)
        rows, _ = self.common.parse_sql_query_res(sql_res3)
        expect = [['2', 'ddd', '18', '12213691470'],
                  ['5', 'eee', '19', '13525801560']]
        self.assertEqual(rows, expect, '执行失败' + text)

        text = '----step6:截断表; expect：成功----'
        self.log.info(text)
        sql_cmd4 = f"truncate table {self.tb_name};"
        self.log.info(sql_cmd4)
        sql_res4 = self.pri_sh.execut_db_sql(sql_cmd4, dbname=self.db_name)
        self.log.info(sql_res4)
        self.assertIn('TRUNCATE', sql_res4)

        text = '----step7:重新创建文件; expect：成功----'
        self.log.info(text)
        echo_cmd = f'''rm -rf {self.file_path};
                       echo "1+aaa+17+13313692580" >> {self.file_path};
                       echo "2+bbb+18+12213691470" >> {self.file_path};
                       echo "3+ccc+19+13525801560" >> {self.file_path};
                       echo "4+ddd+18+12213691470" >> {self.file_path};
                       echo "5+eee+19+13525801560" >> {self.file_path};
                       '''
        self.log.info(echo_cmd)
        res = self.common.get_sh_result(self.pri_sh.node, echo_cmd)
        self.log.info(res)
        self.assertEqual('', res, '文件写入失败')

        text = ('----step8:向创建的表中导入与唯一键不重复的值,其他列数据已经在表中不存在;'
                'expect：成功----')
        self.log.info(text)
        sql_cmd2 = (f"load data infile '{self.file_path}' replace into table "
                    f"{self.tb_name} character set 'sql_ascii' "
                    f"fields terminated by '+' ;")
        self.log.info(sql_cmd2)
        sql_res2 = self.pri_sh.execut_db_sql(sql_cmd2, dbname=self.db_name)
        self.log.info(sql_res2)
        self.assertIn('COPY 5', sql_res2, '执行失败' + text)

        text = '----step9:查询表; expect：成功----'
        self.log.info(text)
        sql_cmd3 = f"select * from {self.tb_name};"
        self.log.info(sql_cmd3)
        sql_res3 = self.pri_sh.execut_db_sql(sql_cmd3, dbname=self.db_name)
        self.log.info(sql_res3)
        rows, _ = self.common.parse_sql_query_res(sql_res3)
        expect = [['1', 'aaa', '17', '13313692580'],
                  ['4', 'ddd', '18', '12213691470'],
                  ['5', 'eee', '19', '13525801560']]
        self.assertEqual(rows, expect, '执行失败' + text)

        text = '----step10:截断表; expect：成功----'
        self.log.info(text)
        sql_cmd4 = f"truncate table {self.tb_name};"
        self.log.info(sql_cmd4)
        sql_res4 = self.pri_sh.execut_db_sql(sql_cmd4, dbname=self.db_name)
        self.log.info(sql_res4)
        self.assertIn('TRUNCATE', sql_res4)

        text = ('----step11:向创建的表中导入与唯一键不重复的值,其他列数据已经在表中存在;'
                'expect：成功----')
        self.log.info(text)
        sql_cmd2 = (f"load data infile '{self.file_path}' replace into table "
                    f"{self.tb_name} character set 'sql_ascii' "
                    f"fields terminated by '+' ;")
        self.log.info(sql_cmd2)
        sql_res2 = self.pri_sh.execut_db_sql(sql_cmd2, dbname=self.db_name)
        self.log.info(sql_res2)
        self.assertIn('COPY 5', sql_res2, '执行失败' + text)

        text = '----step12:重新创建文件; expect：成功----'
        self.log.info(text)
        echo_cmd = f'''rm -rf {self.file_path};
                      echo "6+xiaoming+18+12213691470" >> {self.file_path};
                      echo "7+小名+19+13525801560" >> {self.file_path};
                      echo "8+ddd+18+12213691470" >> {self.file_path};
                      echo "9+eee+19+13525801560" >> {self.file_path};
                      '''
        self.log.info(echo_cmd)
        res = self.common.get_sh_result(self.pri_sh.node, echo_cmd)
        self.log.info(res)
        self.assertEqual('', res, '文件写入失败')

        text = ('----step13:向创建的表中导入与唯一键不重复的值,其他列数据已经在表中存在;'
                'expect：成功----')
        self.log.info(text)
        sql_cmd2 = (f"load data infile '{self.file_path}' replace into table "
                    f"{self.tb_name} character set 'sql_ascii' "
                    f"fields terminated by '+' ;")
        self.log.info(sql_cmd2)
        sql_res2 = self.pri_sh.execut_db_sql(sql_cmd2, dbname=self.db_name)
        self.log.info(sql_res2)
        self.assertIn('COPY 4', sql_res2, '执行失败' + text)

        text = '----step14:查询表; expect：成功----'
        self.log.info(text)
        sql_cmd3 = f"select * from {self.tb_name};"
        self.log.info(sql_cmd3)
        sql_res3 = self.pri_sh.execut_db_sql(sql_cmd3, dbname=self.db_name)
        self.log.info(sql_res3)
        rows, _ = self.common.parse_sql_query_res(sql_res3)
        expect = [['1', 'aaa', '17', '13313692580'],
                  ['8', 'ddd', '18', '12213691470'],
                  ['9', 'eee', '19', '13525801560']]
        self.assertEqual(rows, expect, '执行失败' + text)

    def tearDown(self):
        text = '----step15:清理环境;expect:成功----'
        self.log.info(text)
        sql_cmd6 = f"drop database if exists {self.db_name};"
        self.log.info(sql_cmd6)
        sql_res6 = self.pri_sh.execut_db_sql(sql_cmd6, dbname='postgres')
        self.log.info(sql_res6)
        rm_cmd = f'''rm -rf {self.file_path};'''
        self.log.info(rm_cmd)
        res = self.common.get_sh_result(self.pri_sh.node, rm_cmd)
        self.log.info(res)
        self.assertIn('DROP DATABASE', sql_res6)
        self.log.info(f'----{os.path.basename(__file__)} end----')
